#encoding: utf-8

from flask import (
    Blueprint,
    views,
    render_template,
    request,
    session,
    g,
    redirect,
    url_for,
)
from .forms import (
    LoginForm,
    ResetpwdForm,
    ResetEmailForm,
    AddFeedbackForm,
    UpdateFeedbackForm,
    CreateCMSUserForm,
    UpdateCMSUserForm,
    CreateCaseGroupForm,
    UpdateCaseGroupForm,
    AddCaseForm,
    UpdateCaseForm,
    DebugCaseForm,
    CreateCasePlanForm,
    UpdateCasePlanForm
)
from .models import CMSUser, CMSPermission, CaseGroup, CaseManage, CasePlan, CaseRunRes
from ..models import FeedbackRecordModel as frm
from .decorators import login_required, permission_required
import config
from exts import db, mail, sched
from flask_mail import Message
from utils import restful, amancache, dingtalk
from flask_paginate import Pagination, get_page_parameter
from sqlalchemy import desc
import string
import random
import datetime
import re
import traceback
import xlwt,os,xlrd
from utils.commonFunc import (
    validateDatetime,
    validateDate,
    get_dict_key,
    generateNums,
    replaceMarks,
    singleSelect_ValueToKey,
    checkExcelData,
    isNumLeters,
    is_headers_ok,
    myReplace
)
from utils.getLogs import collectLogs
from utils.runCase import debugCaseFunc
from utils.wayToRunCase import waysToRun
from utils.getCases import getCases_sql
from utils.sqls import ExecuteSql
from utils.charts.pieCharts import myCharts

bp = Blueprint("cms", __name__, url_prefix='/')

@bp.route('/')  # 路由装饰器必须在第一位
@login_required
def index():
    return render_template('cms/cms_index.html')

@bp.route('/logout/')
@login_required
def logout():
    del session[config.CMS_USER_ID]
    return redirect(url_for('cms.login'))

# 用户信息
@bp.route('/profile/')
@login_required
def profile():
    return render_template('cms/cms_profile.html')

# 获取邮箱验证码
@bp.route('/email_captcha/')
def email_captcha():
    email = request.args.get('email')
    if ((not email) or ('@' not in email)):
        collectLogs(loginfo='新邮箱输入不正确，请重新输入！', logger='email_captcha')
        return restful.params_error('新邮箱输入不正确，请重新输入！')
    if g.cms_user.email == email:
        collectLogs(loginfo='新邮箱不能和原邮箱一样，请更换新邮箱！', logger='email_captcha')
        return restful.params_error('新邮箱不能和原邮箱一样，请更换新邮箱！')
    source = list(string.ascii_letters)
    # source.extend(['0','1','2','3','4','5','6','7','8','9'])
    source.extend(map(lambda x:str(x), range(0,10)))
    captcha = ''.join(random.sample(source, 6))
    message = Message('邮件验证码', recipients=[email], body='您的验证码为：%s' %captcha)
    try:
        mail.send(message)
    except:
        return restful.server_error()
    amancache.set(email, captcha, timeout=120)
    collectLogs(loginfo='验证码发送成功，captcha=%s' % captcha, logger='email_captcha')
    return restful.success('验证码发送成功')

# cms用户管理
@bp.route('/cusers/')
@login_required
@permission_required(CMSPermission.CMSUSER)
def cusers():
    # 使用分页器分页
    PER_PAGE = 10
    page = request.args.get(get_page_parameter(), type=int, default=1)
    start = (page - 1) * PER_PAGE
    end = start + PER_PAGE

    total = CMSUser.query.filter_by(isDel=0).count()
    cmsusers = CMSUser.query.filter_by(isDel=0).order_by(desc('join_time')).slice(start, end)
    pagination = Pagination(bs_version=3, page=page, total=total, prev_label='上一页', next_label='下一页')

    context = {
        'total':total,
        'cmsusers': cmsusers,
        'pagination': pagination
    }
    collectLogs(loginfo=context, logger='cusers')
    return render_template('cms/cms_cusers.html', **context)

# 角色管理
@bp.route('/croles/')
@login_required
@permission_required(CMSPermission.ALL_PERMISSION)
def croles():
    return render_template('cms/cms_croles.html')

# feedback
# @bp.route('/feedback/page=<int:page>',strict_slashes=False, defaults={"page": 0})
@bp.route('/feedback/')
@login_required
def feedback():
    searchIssueDesc = request.args.get('p1', type=str, default='all')
    searchFeedbackDate = request.args.get('p2', type=str, default='1000-01-01')
    searchProvider = request.args.get('p3', type=str, default='all')
    searchOrderCode = request.args.get('ordercode', type=str, default='')

    bug_url = config.bug_url
    bug_host = config.bug_host
    business = config.business
    text_max_length = config.TEXT_MAX_LENGTH

    # 使用分页器分页
    PER_PAGE = config.PAGE_SIZE
    page = request.args.get(get_page_parameter(), type=int, default=1)
    start = (page - 1) * PER_PAGE
    end = start + PER_PAGE

    if searchOrderCode:
        total = 1
        feedbacks = frm.query.filter(frm.isDel == 0, frm.order_code == searchOrderCode)
        pagination = Pagination(bs_version=3, page=page, total=total, prev_label='上一页', next_label='下一页')
        context = {
            'bug_url': bug_url,
            'bug_host': bug_host,
            'business': business,
            'total': total,
            'feedbacks': feedbacks,
            'pagination': pagination,
            'text_max_length': text_max_length
        }
        collectLogs(loginfo=context, logger='feedback')
        return render_template('cms/cms_feedback_record.html', **context)

    if (searchIssueDesc == 'all' or searchIssueDesc == '') and (searchFeedbackDate == '1000-01-01') and (searchProvider == 'all' or searchProvider == ''):
        total = frm.query.filter_by(isDel=0).count()
        feedbacks = frm.query.filter_by(isDel=0).order_by(desc('feedback_date')).slice(start, end)
        pagination = Pagination(bs_version=3, page=page, total=total, prev_label='上一页', next_label='下一页')
    else:
        issue_desc = frm.issue_desc
        feedback_date = frm.feedback_date
        provider = frm.provider
        if searchFeedbackDate == '1000-01-01':
            total = frm.query.filter(frm.isDel == 0, issue_desc.contains(searchIssueDesc), provider.contains(searchProvider)).count()
            feedbacks = frm.query.filter(frm.isDel == 0, issue_desc.contains(searchIssueDesc), provider.contains(searchProvider)).order_by(desc('feedback_date')).slice(start, end)
        else:
            total = frm.query.filter(frm.isDel==0, issue_desc.contains(searchIssueDesc), feedback_date==searchFeedbackDate, provider.contains(searchProvider)).count()
            feedbacks = frm.query.filter(frm.isDel==0, issue_desc.contains(searchIssueDesc), feedback_date==searchFeedbackDate, provider.contains(searchProvider)).order_by(desc('feedback_date')).slice(start, end)

        pagination = Pagination(bs_version=3, page=page, total=total, prev_label='上一页', next_label='下一页')
    context = {
        'bug_url': bug_url,
        'bug_host': bug_host,
        'business': business,
        'total': total,
        'feedbacks': feedbacks,
        'pagination': pagination,
        'text_max_length': text_max_length
    }
    collectLogs(loginfo=context, logger='feedback')
    return render_template('cms/cms_feedback_record.html', **context)

@bp.route('/addfeedback/', methods=['POST'])
@login_required
def addfeedback():
    form = AddFeedbackForm(request.form)
    if form.validate():
        feedback_date = form.feedback_date.data
        provider = form.provider.data
        issue_desc = form.issue_desc.data
        reason = form.reason.data
        solution = form.solution.data
        ishandled = form.ishandled.data
        issue_type = form.issue_type.data
        reciver = form.reciver.data
        resolver = form.resolver.data
        remark = form.remark.data
        image_url = form.image_url.data
        issue_lever = form.issue_lever.data
        isBug = form.isBug.data
        bug_url = form.bug_url.data
        influence = form.influence.data
        business = str(form.business.data.split(','))
        create_by = g.cms_user.username
        order_code = 'GD' + generateNums(23)

        feedback = frm(
            feedback_date=feedback_date, provider=provider, issue_desc=issue_desc,
            reason=reason, solution=solution, ishandled=ishandled, issue_type=issue_type,
            reciver=reciver, resolver=resolver, remark=remark, image_url=image_url,
            issue_lever=issue_lever, isBug=isBug, bug_url=bug_url, influence=influence,
            business=business, create_by=create_by, update_by=create_by, order_code=order_code
        )

        try:
            db.session.add(feedback)
            db.session.commit()
            collectLogs(loginfo='添加记录成功，code：%s' % order_code, logger='addfeedback')
            if config.isDingtalk == 1:
                try:
                    issue_lever_num = int(issue_lever)
                    issue_lever = config.issue_lever[issue_lever_num]
                except:
                    issue_lever_num = -1
                    issue_lever = '未设置'
                if issue_lever_num < 3 and issue_lever_num != -1:
                    issue_lever = "<font color=#FF0000>" + issue_lever + "</font>"
                elif issue_lever_num < 5 and issue_lever_num != -1:
                    issue_lever = "<font color=#ff8c00>" + issue_lever + "</font>"
                else:
                    issue_lever = "<font color=#b8860b>" + issue_lever + "</font>"
                messageUrl = config.MESSAGE_URL_RECORD + order_code
                sendRes = dingtalk.sendDingTalkMsg_record(config.DINGTALK_URL, orderCode=order_code, messageUrl=messageUrl, issueDesc=issue_desc, issueLever=issue_lever, provider=provider, atWho=config.ATWHO)
                if sendRes['code'] == 1:
                    collectLogs(loginfo='钉钉消息发送成功！发送结果：%s' % sendRes, logger='addfeedback')
                else:
                    collectLogs(loginfo='钉钉消息发送异常，异常信息：%s' %(sendRes['message']), lever='warning', logger='addfeedback')
        except Exception:
            db.session.rollback()
            collectLogs(loginfo='添加记录失败，原因：%s' % traceback.format_exc(), lever='error', logger='addfeedback')
            return restful.server_error('添加失败！')
        return restful.success('添加成功！')
    else:
        errmsg = form.get_errors()
        collectLogs(loginfo=errmsg, lever='warning', logger='addfeedback')
        return restful.params_error(message=errmsg)

@bp.route('/updatefeedback/', methods=['POST'])
@login_required
def updatefeedback():
    form = UpdateFeedbackForm(request.form)
    if form.validate():
        feedback_id = form.feedback_id.data
        feedback_date = form.feedback_date.data
        provider = form.provider.data
        issue_desc = form.issue_desc.data
        reason = form.reason.data
        solution = form.solution.data
        ishandled = form.ishandled.data
        issue_type = form.issue_type.data
        reciver = form.reciver.data
        resolver = form.resolver.data
        remark = form.remark.data
        image_url = form.image_url.data
        issue_lever = form.issue_lever.data
        isBug = form.isBug.data
        bug_url = form.bug_url.data
        influence = form.influence.data
        business = str(form.business.data.split(','))
        update_by = g.cms_user.username

        feedback = frm.query.get(feedback_id)
        if feedback:
            feedback.feedback_date = feedback_date
            feedback.provider = provider
            feedback.issue_desc = issue_desc
            feedback.reason = reason
            feedback.solution = solution
            feedback.ishandled = ishandled
            feedback.issue_type = issue_type
            feedback.reciver = reciver
            feedback.resolver = resolver
            feedback.remark = remark
            feedback.image_url = image_url
            feedback.issue_lever = issue_lever
            feedback.isBug = isBug
            feedback.bug_url = bug_url
            feedback.influence = influence
            feedback.business = business
            feedback.update_by = update_by

            try:
                db.session.commit()
                collectLogs(loginfo='修改成功，id：%s' % feedback_id, logger='updatefeedback')
            except Exception:
                db.session.rollback()
                collectLogs(loginfo='修改失败，原因：%s' % traceback.format_exc(), lever='error', logger='updatefeedback')
                return restful.server_error('保存修改失败')
            return restful.success('修改成功！')
        else:
            collectLogs(loginfo='查询记录信息异常，id：%s' % feedback_id, lever='warning', logger='updatefeedback')
            return restful.params_error('查询记录信息异常！')
    else:
        return restful.params_error(form.get_errors())

@bp.route('/delfeedback/', methods=['POST'])
@login_required
def delfeedback():
    feedback_id = request.form.get('feedback_id')
    if not feedback_id:
        collectLogs(loginfo='获取记录id异常，id：%s' % feedback_id, lever='warning', logger='delfeedback')
        return restful.params_error('获取记录id异常！')

    feedback = frm.query.get(feedback_id)
    if not feedback:
        collectLogs(loginfo='查询记录信息异常，id：%s' % feedback_id, lever='warning', logger='delfeedback')
        return restful.params_error('查询记录信息异常！')

    # 删除功能暂不开放
    # feedback.isDel = 1 # 逻辑删除
    # try:
    #     # db.session.delete(feedback) # 物理删除
    #     db.session.commit()
    #     collectLogs(loginfo='记录删除成功，id：%s' % feedback_id, lever='info', logger='delfeedback')
    # except Exception:
    #     db.session.rollback()
    #     collectLogs(loginfo='记录删除，id：%s，原因：%s' % (feedback_id, traceback.format_exc()), lever='error', logger='delfeedback')
    #     return restful.server_error('记录删除失败！')
    # return restful.success('删除成功！')
    return restful.params_error('删除失败，删除功能暂不提供使用！')

# 导出
@bp.route('/exportrecords/', methods=['POST'])
@login_required
def exportrecords():
    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet('反馈问题记录表')
    # 设置格式
    font = xlwt.Font()
    font.colour_index = 2  # 代号2为红色
    font.bold = True  # 加粗
    style = xlwt.easyxf()
    style.font = font

    ws.write_merge(0, 0, 0, 8,config.IMPORTTEXT)
    ws.row(0).set_style(xlwt.easyxf('font:height 500;'))

    weight = 25
    ws.col(0).width = 50 * weight
    ws.col(1).width = 120 * weight
    ws.col(2).width = 100 * weight
    ws.col(3).width = 100 * weight
    ws.col(4).width = 400 * weight
    ws.col(5).width = 400 * weight
    ws.col(6).width = 250 * weight
    ws.col(7).width = 100 * weight
    ws.col(8).width = 100 * weight
    ws.col(9).width = 100 * weight
    ws.col(10).width = 100 * weight
    ws.col(11).width = 230 * weight
    ws.col(12).width = 230 * weight
    ws.col(13).width = 100 * weight
    ws.col(14).width = 100 * weight
    ws.col(15).width = 100 * weight
    ws.col(16).width = 100 * weight
    ws.col(17).width = 100 * weight
    ws.col(18).width = 100 * weight
    ws.col(19).width = 230 * weight
    ws.col(20).width = 230 * weight
    ws.write(1, 0, "工单编码*", style)
    ws.write(1, 1, "反馈日期*", style)
    ws.write(1, 2, "反馈人*", style)
    ws.write(1, 3, "所属业务线*", style)
    ws.write(1, 4, "问题描述*", style)
    ws.write(1, 5, "问题原因")
    ws.write(1, 6, "解决方案")
    ws.write(1, 7, "解决状态*", style)
    ws.write(1, 8, "问题类型*", style)
    ws.write(1, 9, "接收人*", style)
    ws.write(1, 10, "解决人")
    ws.write(1, 11, "备注")
    ws.write(1, 12, "图片地址")
    ws.write(1, 13, "严重程度*", style)
    ws.write(1, 14, "是否为bug*", style)
    ws.write(1, 15, "bug_id")
    ws.write(1, 16, "影响范围")
    ws.write(1, 17, "创建人*", style)
    ws.write(1, 18, "更新人*", style)
    ws.write(1, 19, "创建时间*", style)
    ws.write(1, 20, "更新时间*", style)
    dataw = frm.query.order_by(desc('feedback_date')).filter_by(isDel=0).all()
    if dataw is not None:
        for i in range(1, len(dataw)+1):
            pet = dataw[i-1]
            feedback_date = str(pet.feedback_date.strftime('%Y-%m-%d'))
            create_at = pet.create_at.strftime('%Y-%m-%d %H:%M:%S')
            update_at = pet.update_at.strftime('%Y-%m-%d %H:%M:%S')
            business = ''
            businessList = [int(s) for s in re.findall(r'\b\d+\b', str(pet.business))]
            for item in businessList:
                try:
                    business = business + (config.business[int(item)]) + '，'
                except:
                    err = '业务线(' + str(item) + ')不存在，'
                    business = business + err
            business = business[:-1]
            ws.write(i + 1, 0, pet.order_code)
            ws.write(i + 1, 1, feedback_date)
            ws.write(i + 1, 2, pet.provider)
            ws.write(i + 1, 3, business)
            ws.write(i + 1, 4, pet.issue_desc)
            ws.write(i + 1, 5, pet.reason)
            ws.write(i + 1, 6, pet.solution)
            ws.write(i + 1, 7, config.ishandled[pet.ishandled])
            ws.write(i + 1, 8, config.issue_type[pet.issue_type])
            ws.write(i + 1, 9, pet.reciver)
            ws.write(i + 1, 10, pet.resolver)
            ws.write(i + 1, 11, pet.remark)
            ws.write(i + 1, 12, pet.image_url)
            ws.write(i + 1, 13, config.issue_lever[pet.issue_lever])
            ws.write(i + 1, 14, config.isBug[pet.isBug])
            ws.write(i + 1, 15, pet.bug_url)
            ws.write(i + 1, 16, pet.influence)
            ws.write(i + 1, 17, pet.create_by)
            ws.write(i + 1, 18, pet.update_by)
            ws.write(i + 1, 19, create_at)
            ws.write(i + 1, 20, update_at)
    else:
        ws.write(2, 0, '导出记录为空，请检查后重新导出！')
    fileName = "feedbackRecords_" + datetime.datetime.now().strftime('%Y%m%d%H%M%S') + ''.join(random.sample('04512346789567012389', 4)) + ".xls"
    file_path = config.BASE_DIR + config.path_download
    if not os.path.exists(file_path):
        os.makedirs(file_path)
    file_path = file_path + fileName
    try:
        f = open(file_path, 'r')
        f.close()
    except IOError:
        f = open(file_path, 'w')
    wb.save(file_path)
    return config.path_download + fileName

@bp.route('/uploadfilepage/', methods=['GET'])
@login_required
def uploadfilepage():
    content = {
        # 'msg':msg
    }
    return render_template('cms/cms_uploadfilepage.html', **content)

# 上传文件
@bp.route('/uploadfile/', methods=['POST'])
@login_required
def uploadfile():
    # 将请求的ImmutableMultiDict类型数据转为字典dict格式，然后再处理数据
    filePath = dict(request.form)['excelfile'][0]
    collectLogs(loginfo='filePath为：%s' %(filePath, ), logger='uploadfile')
    if not filePath:
        return '没有找到excel文件，请检查！'
    # 文件上传到服务器后获取导入文件的地址
    # os.path.join(config.BASE_DIR, filePath)

    checkRes = checkExcelData(filePath)
    if checkRes != 1 and not config.IMPORT_WAY:
        return checkRes
    try:
        feedbacksInfo = xlrd.open_workbook(filePath)
        firstSheetName = feedbacksInfo.sheet_names()[0]
        if '反馈问题记录表' == firstSheetName:
            feedbackSheet = feedbacksInfo.sheet_by_name(firstSheetName)
            dataRows = feedbackSheet.nrows

            failedRowList = []
            resultMsg = []

            if dataRows > config.IMPORT_MAX_ROWS + 2:
                collectLogs(loginfo='获取到 %d 行数据，超过1000行，将只导入除表头以外的前1000行数据。' % (dataRows - 2, ), logger='uploadfile')
                dataRows = 1002
            for i in range(2, dataRows):
                order_code = feedbackSheet.cell(i,0).value
                feedback_date = feedbackSheet.cell(i,1).value
                provider = str(feedbackSheet.cell(i,2).value)
                business = feedbackSheet.cell(i,3).value
                issue_desc = str(feedbackSheet.cell(i,4).value)
                reason = str(feedbackSheet.cell(i,5).value)
                solution = str(feedbackSheet.cell(i,6).value)
                ishandled = str(feedbackSheet.cell(i,7).value)
                issue_type = str(feedbackSheet.cell(i,8).value)
                reciver = str(feedbackSheet.cell(i,9).value)
                resolver = str(feedbackSheet.cell(i,10).value)
                remark = str(feedbackSheet.cell(i,11).value)
                image_url = str(feedbackSheet.cell(i,12).value)
                issue_lever = str(feedbackSheet.cell(i,13).value)
                isBug = str(feedbackSheet.cell(i,14).value)
                bug_url = feedbackSheet.cell(i,15).value
                influence = str(feedbackSheet.cell(i,16).value)
                create_by = str(feedbackSheet.cell(i,17).value)
                update_by = str(feedbackSheet.cell(i,18).value)
                create_at = feedbackSheet.cell(i,19).value
                update_at = feedbackSheet.cell(i,20).value

                # 数据入库前校验和预处理
                if config.IMPORT_WAY and (not feedback_date or not provider or not issue_desc or not reciver):
                    collectLogs(loginfo='第 %d 行数据有必填项为空，直接跳过！' %(i+1, ), lever='warning', logger='uploadfile')
                    failedRowList.append(i+1)
                    continue

                if feedback_date and not validateDate(feedback_date):
                    if feedbackSheet.cell(i,1).ctype == 3:
                        date = xlrd.xldate_as_datetime(feedback_date, 0)
                        feedback_date = date.strftime('%Y-%m-%d')
                    else:
                        feedback_date = '1970-01-01'

                now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                if create_at:
                    if not validateDatetime(create_at):
                        if feedbackSheet.cell(i, 19).ctype == 3:
                            create_at = xlrd.xldate_as_datetime(create_at, 0)
                        else:
                            create_at = now_time
                            collectLogs(loginfo='第 %d 行创建时间格式错误，已取当前时间！' % (i + 1, ), lever='warning', logger='uploadfile')
                else:
                    create_at = now_time
                    collectLogs(loginfo='第 %d 行创建时间为空，取当前时间！' % (i + 1, ), lever='warning', logger='uploadfile')

                if update_at:
                    if not validateDatetime(update_at):
                        if feedbackSheet.cell(i, 20).ctype == 3:
                            update_at = xlrd.xldate_as_datetime(update_at, 0)
                        else:
                            update_at = now_time
                            collectLogs(loginfo='第 %d 行更新时间格式错误，已取当前时间！' % (i + 1, ), lever='warning', logger='uploadfile')
                else:
                    update_at = now_time
                    collectLogs(loginfo='第 %d 行更新时间为空，取当前时间！' % (i + 1, ), lever='warning', logger='uploadfile')

                # bugid若为浮点数会取整数部分，主要处理导出文件中双击bugid单元格后出现的浮点数
                if feedbackSheet.cell(i,15).ctype == 2:
                    bug_url = str(int(bug_url))

                # 如果创建人和更新人为空，取当前登录用户，获取当前用户失败默认为管理员
                current_user = g.cms_user.username
                if not create_by:
                    if current_user:
                        create_by = current_user
                    else:
                        create_by = '管理员Auto'
                if not update_by:
                    if current_user:
                        update_by = current_user
                    else:
                        update_by = '管理员Auto'

                # 处理字典的value转为key
                business = replaceMarks(business, '，').split('，')
                businessKeys = []
                for item in business:
                    item = item.lower()
                    if item not in config.business.values():
                        businessKeys.append('1')
                    else:
                        businessKeys.append(str(get_dict_key(config.business, item)[0]))
                businessKeys = str(list(set(businessKeys)))
                ishandledKey = singleSelect_ValueToKey(ishandled, config.ishandled, 0)
                issue_typeKey = singleSelect_ValueToKey(issue_type, config.issue_type, 0)
                issue_leverKey = singleSelect_ValueToKey(issue_lever, config.issue_lever, -1)
                isBugKey = singleSelect_ValueToKey(isBug, config.isBug, -1)

                # 数据入库
                selectSql = "select id from feedback_record where order_code='" + order_code + "'"
                feedback_id = db.session.execute(selectSql).first()
                feedback = frm.query.get(feedback_id)
                if feedback:
                    # 更新
                    feedback.feedback_date = feedback_date
                    feedback.provider = provider
                    feedback.issue_desc = issue_desc
                    feedback.reason = reason
                    feedback.solution = solution
                    feedback.ishandled = ishandledKey
                    feedback.issue_type = issue_typeKey
                    feedback.reciver = reciver
                    feedback.resolver = resolver
                    feedback.remark = remark
                    feedback.image_url = image_url
                    feedback.issue_lever = issue_leverKey
                    feedback.isBug = isBugKey
                    feedback.bug_url = bug_url
                    feedback.influence = influence
                    feedback.business = businessKeys
                    feedback.create_by = create_by
                    feedback.update_by = update_by
                    feedback.create_at = create_at
                    feedback.update_at = update_at
                    try:
                        collectLogs(loginfo='第 %d 行工单编号在库中已存在，正在更新...' % (i + 1,), lever='info', logger='uploadfile')
                        db.session.commit()
                        collectLogs(loginfo='第 %d 行更新成功！' % (i + 1,), lever='info', logger='uploadfile')
                    except Exception as e:
                        db.session.rollback()
                        failedRowList.append(i + 1)
                        collectLogs(loginfo='第 %d 行更新失败！原因：%s' % (i + 1, e), lever='error', logger='uploadfile')
                    continue
                else:
                    # 插入
                    collectLogs(loginfo='第 %d 行工单编号在库中不存在，正在插入...' % (i + 1,), lever='info', logger='uploadfile')
                    order_code = 'GD' + generateNums(23)
                    try:
                        feedback = frm(
                            order_code=order_code, feedback_date=feedback_date, provider=provider, issue_desc=issue_desc,
                            reason=reason, solution=solution, ishandled=ishandledKey, issue_type=issue_typeKey,
                            reciver=reciver, resolver=resolver, remark=remark, image_url=image_url,issue_lever=issue_leverKey,
                            isBug=isBugKey, bug_url=bug_url, influence=influence, business=businessKeys, create_by=create_by,
                            update_by=create_by, create_at=create_at, update_at=update_at, isDel=0
                        )
                        db.session.add(feedback)
                        db.session.commit()
                        collectLogs(loginfo='第 %d 行插入成功！新工单编号：%s' % (i + 1, order_code), lever='info', logger='uploadfile')
                    except Exception as e:
                        db.session.rollback()
                        failedRowList.append(i + 1)
                        collectLogs(loginfo='第 %d 行更新失败！原因：%s' % (i + 1, e), lever='error', logger='uploadfile')
                    continue

            resultMsg.append('导入完成！')
            failedCount = len(failedRowList)
            if failedCount == 0:
                resultMsg.append('全部导入成功，共 %d 条' % (dataRows - 2))
            else:
                resultMsg.append('导入成功 %d 条' %(dataRows - failedCount - 2))
                resultMsg.append('导入失败 %d 条' %(failedCount))
                resultMsg.append('导入失败的行：%s' %(str(failedRowList)))
            return str(resultMsg)
        else:
            return '失败！表格的第一个sheet名称必须为“反馈问题记录表”'
    except Exception as e:
        return e

# 登录
class LoginView(views.MethodView):
    def get(self, message=None): # message可传可不传
        return render_template('cms/cms_login.html', message=message)

    def post(self):
        form = LoginForm(request.form)
        if form.validate():
            useraccount = form.useraccount.data
            password = form.password.data
            remember = form.remember.data
            user = CMSUser.query.filter_by(useraccount=useraccount).first()
            if user and user.check_password(password):
                if user.status == 0 or user.isDel == 1:
                    return self.get(message='帐号不可用，请联系管理员！')
                session[config.CMS_USER_ID] = user.id
                if remember:
                    # session.permanent=True时，过期时间为31天
                    session.permanent = True
                return redirect(url_for('cms.index'))
            else:
                return self.get(message='帐号或密码错误')
        else:
            collectLogs(loginfo='登录验证失败，错误信息：%s' % (form.get_errors(), ), lever='warning', logger='LoginView')
            return self.get(message='登录验证失败')

# 修改密码
class ResetPwdView(views.MethodView):
    decorators = [login_required]  # 装饰器，在重启服务后刷新页面时可以跳转到登录页面
    def get(self):
        return render_template('cms/cms_resetpwd.html')

    def post(self):
        form = ResetpwdForm(request.form)
        if form.validate():
            oldpwd = form.oldpwd.data
            newpwd = form.newpwd.data
            user = g.cms_user
            if user.check_password(oldpwd):
                if oldpwd != newpwd:
                    user.password = newpwd
                    try:
                        db.session.commit()
                    except Exception as e:
                        db.session.rollback()
                        raise e
                    # return jsonify({"code":1, "message":"密码修改成功"})
                    return restful.success('密码修改成功')
                else:
                    return restful.params_error('新密码不能和原密码一样')
            else:
                return restful.params_error("原密码错误！")
        else:
            return restful.params_error(form.get_errors())

# 修改邮箱
class ResetEmailView(views.MethodView):
    decorators = [login_required]
    def get(self):
        return render_template('cms/cms_restemail.html')

    def post(self):
        form = ResetEmailForm(request.form)
        if form.validate():
            email = form.email.data
            g.cms_user.email = email
            try:
                db.session.commit()
            except Exception as e:
                db.session.rollback()
                raise e
            return restful.success('邮箱修改成功！')
        else:
            return restful.params_error(form.get_errors())

# 新增cms用户
@bp.route('/addcmsuser/', methods=['POST'])
@login_required
def addcmsuser():
    form = CreateCMSUserForm(request.form)
    if form.validate():
        username = form.username.data
        useraccount = form.useraccount.data
        password = form.password.data
        email = form.email.data
        mobile = form.mobile.data
        create_by = g.cms_user.username

        if not isNumLeters(useraccount):
            return restful.params_error('帐号格式错误：帐号只能以字母或数字开头\n并且只能包含字母、数字、中划线、下划线中的一个或多个')
        cu_account = CMSUser.query.filter_by(useraccount=useraccount).first()
        if cu_account:
            return restful.params_error('帐号已被占用，请更换后重新提交！')
        cu_mobile = CMSUser.query.filter_by(mobile=mobile).first()
        if cu_mobile:
            return restful.params_error('手机号已被占用，请更换后重新提交！')
        cu_email = CMSUser.query.filter_by(email=email).first()
        if cu_email:
            return restful.params_error('邮箱已被占用，请更换后重新提交！')
        if not create_by:
            create_by = '管理员Auto'
        user = CMSUser(username=username, useraccount=useraccount, password=password, email=email, mobile=mobile, create_by=create_by, update_by=create_by)
        try:
            db.session.add(user)
            db.session.commit()
            body = '你的小麻雀综合测试平台帐号开通成功！\n登录帐号为：%s，密码为：%s【请尽快登录系统修改密码】' % (email, password)
            message = Message('帐号开通成功', recipients=[email], body=body)
            try:
                mail.send(message)
                collectLogs(loginfo='邮件提醒发送成功', lever='info', logger='addcmsuser')
            except Exception as e:
                collectLogs(loginfo='邮件发送失败，原因：%s' %str(e), lever='error', logger='addcmsuser')
        except Exception as e:
            db.session.rollback()
            collectLogs(loginfo='更新失败，原因：%s' % str(e), lever='error', logger='addcmsuser')
            return restful.server_error()
        return restful.success('用户新增成功！')
    else:
        return restful.params_error(form.get_errors())

# 编辑用户信息
@bp.route('/updatecmsuser/', methods=['POST'])
@login_required
def updatecmsuser():
    form = UpdateCMSUserForm(request.form)
    if form.validate():
        cmsuser_id = form.cmsuser_id.data
        username = form.username.data
        email = form.email.data
        mobile = form.mobile.data
        update_by = g.cms_user.username
        if not update_by:
            update_by = '管理员Auto'

        cmsuser = CMSUser.query.get(cmsuser_id)
        if cmsuser:
            cu_mobile = CMSUser.query.filter_by(mobile=mobile).first()
            if cu_mobile and mobile != cmsuser.mobile:
                return restful.params_error('手机号已被占用，请更换后重新提交！')
            cu_email = CMSUser.query.filter_by(email=email).first()
            if cu_email and email != cmsuser.email:
                return restful.params_error('邮箱已被占用，请更换后重新提交！')
            cmsuser.username = username
            cmsuser.email = email
            cmsuser.mobile = mobile
            cmsuser.update_by = update_by
            try:
                db.session.commit()
            except Exception as e:
                db.session.rollback()
                collectLogs(loginfo='更新用户信息失败，原因：%s' % str(e), lever='error', logger='updatecmsuser')
                return restful.server_error('用户信息更新失败，请重试！\n若多次重试均失败，请联系管理员处理！')
            return restful.success('修改成功！')
        else:
            return restful.params_error('记录不存在！')
    else:
        return restful.params_error(form.get_errors())

# 更新状态
@bp.route('/updatestatus/', methods=['POST'])
@login_required
def updatestatus():
    cmsuser_id = request.form.get('cmsuser_id')
    status = request.form.get('status')
    cmsuser = CMSUser.query.get(cmsuser_id)
    if cmsuser:
        tem = cmsuser.status
        cmsuser.status = status
        try:
            db.session.commit()
        except Exception:
            db.session.rollback()
            collectLogs(loginfo='更新用户状态失败，原因：%s' % traceback.format_exc(), lever='error', logger='updatestatus')
            return restful.server_error('用户状态更新失败，请重试！\n若多次重试均失败，请联系管理员处理！')
        collectLogs(loginfo='状态更新成功，更新前：%s，更新后：%s' %(tem, status), logger='updatestatus')
        return restful.success()
    else:
        collectLogs(loginfo='更新状态失败，用户不存在！，用户id：%s' % str(cmsuser_id), lever='warning', logger='updatestatus')
        return restful.params_error('用户不存在！')

# 删除用户
@bp.route('/delcmsuser/', methods=['POST'])
@login_required
def delcmsuser():
    cmsuser_id = request.form.get('cmsuser_id')
    if not cmsuser_id:
        collectLogs(loginfo='没有找到用户id：%s' % str(cmsuser_id), lever='warning', logger='delcmsuser')
        return restful.params_error('没有找到用户id！')
    cmsuser = CMSUser.query.get(cmsuser_id)
    if not cmsuser:
        collectLogs(loginfo='删除失败，没有找到用户，用户id：%s' % str(cmsuser_id), lever='warning', logger='delcmsuser')
        return restful.params_error('没有这个用户或已删除！')
    cmsuser.isDel = 1 # 逻辑删除
    try:
        db.session.commit()
    except Exception:
        db.session.rollback()
        collectLogs(loginfo='删除失败，原因：%s' % traceback.format_exc(), lever='error', logger='delcmsuser')
        return restful.server_error()
    collectLogs(loginfo='删除成功，用户id：%s' % str(cmsuser_id), logger='delcmsuser')
    return restful.success('删除成功！')

# 重置密码
@bp.route('/resetpassword/', methods=['POST'])
@login_required
def resetpassword():
    cmsuser_id = request.form.get('cmsuser_id')
    cmsuser = CMSUser.query.get(cmsuser_id)
    if cmsuser:
        # source = list(string.ascii_letters)  # 字母
        source = []
        source.extend(map(lambda x: str(x), range(0, 10)))
        captcha = ''.join(random.sample(source, 6))
        cmsuser.password = captcha
        email = cmsuser.email
        try:
            db.session.commit()
            body = '你的小麻雀综合测试平台帐号密码已重置，新密码为：%s【请尽快登录系统修改密码】' %captcha
            message = Message('密码重置成功', recipients=[email], body=body)
            try:
                mail.send(message)
                messageInfo = '密码重置成功，邮件通知发送成功！'
            except Exception:
                collectLogs(loginfo='密码重置成功，邮件发送失败，原因：%s' % traceback.format_exc(), lever='warning', logger='resetpassword')
                messageInfo = '密码重置成功，邮件通知发送失败\n新密码：%s' %captcha
        except Exception:
            db.session.rollback()
            collectLogs(loginfo='密码重置失败，原因：%s' % traceback.format_exc(), lever='error', logger='resetpassword')
            return restful.server_error('密码重置失败，请重试！\n若多次重置均失败，请联系管理员处理！')
        collectLogs(loginfo=messageInfo, logger='resetpassword')
        return restful.success(messageInfo)
    else:
        collectLogs(loginfo='用户不存在，用户id：%s' % str(cmsuser_id), logger='resetpassword')
        return restful.params_error('获取用户异常！')


# ########################### 以下为sparrow接口自动化相关的代码 ################################
# 用例分组
@bp.route('/case_group/')
@login_required
# @permission_required(CMSPermission.CMSUSER)
def case_group():
    PER_PAGE = 10
    page = request.args.get(get_page_parameter(), type=int, default=1)
    start = (page - 1) * PER_PAGE
    end = start + PER_PAGE
    total = CaseGroup.query.filter_by(isDel=0).count()
    groups = CaseGroup.query.filter_by(isDel=0).order_by(desc('create_at')).slice(start, end)
    pagination = Pagination(bs_version=3, page=page, total=total, prev_label='上一页', next_label='下一页')
    context = {
        'total': total,
        'groups': groups,
        'pagination': pagination
    }
    collectLogs(loginfo=context, logger='groups')
    return render_template('cms/case_group.html', **context)

# 新增用例分组
@bp.route('/addcasegroup/', methods=['POST'])
@login_required
def addcasegroup():
    form = CreateCaseGroupForm(request.form)
    if form.validate():
        groupname = form.groupname.data
        groupcode = form.groupcode.data
        create_by = g.cms_user.username
        tem_groupcode = myReplace(groupcode, '&', '', -1, '1')
        if not isNumLeters(tem_groupcode):
            return restful.params_error('分组编码格式错误\n只能以字母或数字开头，并且只能包含字母、数字、减号、下划线、&符号！')
        db_group_by_name = CaseGroup.query.filter_by(group_name=groupname).first()
        db_group_by_code = CaseGroup.query.filter_by(group_code=groupcode).first()
        if db_group_by_name:
            return restful.params_error('分组名已被占用，请更换后重新提交！')
        if db_group_by_code:
            return restful.params_error('分组编码已被占用，请更换后重新提交！')
        if not create_by:
            create_by = '管理员Auto'
        group = CaseGroup(group_name=groupname, group_code=groupcode, create_by=create_by, update_by=create_by)
        try:
            db.session.add(group)
            db.session.commit()
            collectLogs(loginfo='【%s】创建用例分组【%s】成功' %(create_by, groupname), lever='info', logger='addcasegroup')
        except Exception:
            db.session.rollback()
            collectLogs(loginfo='修改分组失败，原因：%s' % traceback.format_exc(), lever='error', logger='addcasegroup')
            return restful.server_error()
        return restful.success('用例分组新增成功！')
    else:
        return restful.params_error(form.get_errors())

# 编辑用例分组
@bp.route('/updatecasegroup/', methods=['POST'])
@login_required
def updatecasegroup():
    form = UpdateCaseGroupForm(request.form)
    if form.validate():
        group_id = form.group_id.data
        groupname = form.groupname.data
        update_by = g.cms_user.username
        if not update_by:
            update_by = '管理员Auto'
        group = CaseGroup.query.get(group_id)
        if group:
            tem = group.group_name
            db_group = CaseGroup.query.filter_by(group_name=groupname).first()
            if db_group and db_group.group_id != group_id:
                collectLogs(loginfo='用例分组【%s】已存在' % (groupname), lever='info', logger='updatecasegroup')
                return restful.params_error('分组名已被占用，请更换后重新提交！')
            group.group_name = groupname
            group.update_by = update_by
            try:
                db.session.commit()
                collectLogs(loginfo='用例分组修改成功，修改前：%s，修改后：%s，操作人：%s' % (tem, groupname,update_by), lever='info', logger='updatecasegroup')
            except Exception:
                db.session.rollback()
                collectLogs(loginfo='修改分组失败，原因：%s' % traceback.format_exc(), lever='error', logger='updatecasegroup')
                return restful.server_error('分组信息更新失败，请重试！\n若多次重试均失败，请联系管理员处理！')
            return restful.success('修改成功！')
        else:
            collectLogs(loginfo='用group_id查询不到记录。group_id=%s' %group_id, lever='error', logger='updatecasegroup')
            return restful.params_error('记录不存在！')
    else:
        msg = form.get_errors()
        collectLogs(loginfo='更新异常，异常信息：%s' %msg, lever='error', logger='updatecasegroup')
        return restful.params_error(msg)

# 更新用例分组状态
@bp.route('/updatestatus_gp/', methods=['POST'])
@login_required
def updatestatus_gp():
    group_id = request.form.get('group_id')
    status = request.form.get('status')
    group = CaseGroup.query.get(group_id)
    if group:
        tem = group.status
        group.status = status
        try:
            db.session.commit()
        except Exception:
            db.session.rollback()
            collectLogs(loginfo='更新用例分组状态失败，原因：%s' % traceback.format_exc(), lever='error', logger='updatestatus_gp')
            return restful.server_error('用例分组状态更新失败，请重试！\n若多次重试均失败，请联系管理员处理！')
        collectLogs(loginfo='用例分组状态更新成功，更新前：%s，更新后：%s' %(tem, status), logger='updatestatus_gp')
        return restful.success()
    else:
        collectLogs(loginfo='更新用例分组状态失败，用例分组不存在！，用户id：%s' % str(group_id), lever='warning', logger='updatestatus_gp')
        return restful.params_error('用例分组不存在！')

# 删除用例分组
@bp.route('/delcasegroup/', methods=['POST'])
@login_required
def delcasegroup():
    group_id = request.form.get('group_id')
    if not group_id:
        collectLogs(loginfo='没有找到用例分组id：%s' % str(group_id), lever='warning', logger='delcasegroup')
        return restful.params_error('没有找到用例分组id！')
    group = CaseGroup.query.get(group_id)
    case_plan = CasePlan.query.filter_by(plan_group=group_id).first()
    case = CaseManage.query.filter_by(group_id=group_id).first()
    if not group:
        collectLogs(loginfo='删除失败，该用例分组不存在或已删除，分组id：%s' % str(group_id), lever='warning', logger='delcasegroup')
        return restful.params_error('删除失败，该用例分组不存在或已删除！')
    if case:
        collectLogs(loginfo='该分组有关联的用例，不允许删除！分组id：%s' % str(group_id), lever='warning', logger='delcasegroup')
        return restful.params_error('该分组有关联的用例，不允许删除！')
    if case_plan:
        collectLogs(loginfo='该分组有关联的用例计划，不允许删除！分组id：%s' % str(group_id), lever='warning', logger='delcasegroup')
        return restful.params_error('该分组有关联的用例计划，不允许删除！')
    group.isDel = 1 # 逻辑删除
    try:
        db.session.commit()
        collectLogs(loginfo='删除成功，用例分组id：%s' % str(group_id), logger='delcasegroup')
    except Exception:
        db.session.rollback()
        collectLogs(loginfo='删除失败，原因：%s' % traceback.format_exc(), lever='error', logger='delcasegroup')
        return restful.server_error()
    return restful.success('删除成功！')

# 用例管理
@bp.route('/case_manage/')
@login_required
# @permission_required(CMSPermission.CMSUSER)
def case_manage():
    tem_groups = {}
    groups = CaseGroup.query.filter_by(isDel=0).order_by(desc('create_at')).limit(200)
    for group in groups:
        tem_groups[group.group_id] = group.group_name

    queryCaseId = request.args.get('caseid', type=str, default='')
    query_url = request.args.get('url', type=str, default='')
    query_param = request.args.get('param', type=str, default='')
    query_case_name = request.args.get('p1', type=str, default='')
    query_group = request.args.get('p2', type=str, default='0')
    cases = ExecuteSql(0, param1=query_case_name, param2=query_group, param3=queryCaseId, param4=query_url, param5=query_param)

    PER_PAGE = 10
    page = request.args.get(get_page_parameter(), type=int, default=1)
    start = (page - 1) * PER_PAGE
    end = start + PER_PAGE
    total = len(cases)
    cases = cases[start:end]
    pagination = Pagination(bs_version=3, page=page, total=total, prev_label='上一页', next_label='下一页')
    context = {
        'total': total,
        'cases': cases,
        'tem_groups':tem_groups,
        'pagination': pagination
    }
    collectLogs(loginfo=context, logger='case_manage')
    return render_template('cms/case_manage.html', **context)

# 新增用例
class addCaseView(views.MethodView):
    decorators = [login_required]  # 装饰器，在重启服务后刷新页面时可以跳转到登录页面
    def get(self, message=None):
        groups = CaseGroup.query.filter_by(isDel=0).order_by(desc('create_at')).limit(200)
        context = {
            'groups': groups,
        }
        return render_template('cms/case_addcase.html', **context)
    def post(self):
        form = AddCaseForm(request.form)
        if form.validate():
            case_name = form.case_name.data
            case_param = form.case_param.data
            case_url = form.case_url.data
            expect_res = form.expect_res.data
            header = form.headers.data
            remark = form.remark.data
            request_method = form.request_method.data
            status = form.status.data
            # 处理用例编码
            group = form.group.data
            if group < 1:
                return restful.params_error('请选择用例所属的分组！')

            case_byName = CaseManage.query.filter_by(case_name=case_name).first()
            if case_byName:
                return restful.params_error('用例名称【%s】已被占用，请更换！' %(case_name,))

            group_code = CaseGroup.query.filter_by(group_id=group).first().group_code
            case_sign = group_code + '-' + datetime.datetime.now().strftime('%Y%m%d%f') + str(random.randint(0, 999999))
            case_bySign = CaseManage.query.filter_by(case_sign=case_sign).first()
            if case_bySign:
                case_sign = case_sign + str(random.randint(0, 999999))
            if len(case_sign) > 50:
                case_sign = case_sign[:40] + str(random.randint(0, 999999))
            # 处理header
            errorHeaders = is_headers_ok(header)
            if len(errorHeaders) > 0:
                collectLogs(loginfo='header头格式不正确，请检查：%s' %errorHeaders, lever='warning', logger='addCaseView')
                return restful.params_error('下面的header头格式不正确，请检查！\n%s' %errorHeaders)
            create_by = g.cms_user.username
            addcase_info = CaseManage(case_name=case_name, case_path=case_url, case_parm=case_param, case_sign=case_sign,
                                 request_method=request_method, expect_res=expect_res, headers=header, remark=remark, status=status,
                                 create_by=create_by, update_by=create_by, group_id=group)
            if addcase_info:
                try:
                    db.session.add(addcase_info)
                    db.session.commit()
                    collectLogs(loginfo='新增用例成功【%s-%s】' %(case_name, case_sign), lever='info', logger='addCaseView')
                except Exception:
                    db.session.rollback()
                    collectLogs(loginfo='新增失败，原因：%s' % traceback.format_exc(), lever='error', logger='addCaseView')
                    return restful.server_error()
                return restful.success('用例增加成功')
            else:
                return restful.params_error("新增用例失败，请重试！")
        else:
            return restful.params_error(form.get_errors())

# 编辑用例
@bp.route('/updatecase/', methods=['POST'])
@login_required
def updatecase():
    form = UpdateCaseForm(request.form)
    if form.validate():
        case_id = form.case_id.data
        case_name = form.case_name.data
        case_url = form.case_url.data
        request_method = form.request_method.data
        case_param = form.case_param.data
        case_group = form.group.data
        expect_res = form.expect_res.data
        header = form.headers.data
        remark = form.remark.data
        status = form.status.data
        update_by = g.cms_user.username
        if not update_by:
            update_by = '管理员Auto'

        case = CaseManage.query.get(case_id)
        if case:
            case_byName = CaseManage.query.filter_by(case_name=case_name).first()
            if case_byName and case_name != case.case_name:
                return restful.params_error('用例名称【%s】已被占用，请更换！' % (case_name,))

            # 处理header
            errorHeaders = is_headers_ok(header)
            if len(errorHeaders) > 0:
                collectLogs(loginfo='header头格式不正确，请检查：%s' % errorHeaders, lever='warning', logger='updatecase')
                return restful.params_error('下面的header头格式不正确，请检查！\n%s' % errorHeaders)
            case.case_name = case_name
            case.case_path = case_url
            case.case_parm = case_param
            case.request_method = request_method
            case.expect_res = expect_res
            case.headers = header
            case.remark = remark
            case.status = status
            case.group_id = case_group
            case.update_by = update_by
            try:
                db.session.commit()
                collectLogs(loginfo='用例修改成功，操作人：%s' % (update_by), lever='info', logger='updatecase')
            except Exception:
                db.session.rollback()
                collectLogs(loginfo='修改分组失败，原因：%s' % traceback.format_exc(), lever='error', logger='updatecase')
                return restful.server_error('用例信息更新失败，请重试！\n若多次重试均失败，请联系管理员处理！')
            return restful.success('修改成功！')
        else:
            collectLogs(loginfo='用case_id查询不到记录。case_id=%s' % case_id, lever='error', logger='updatecase')
            return restful.params_error('记录不存在！')
    else:
        msg = form.get_errors()
        collectLogs(loginfo='更新异常，异常信息：%s' % msg, lever='error', logger='updatecase')
        return restful.params_error(msg)

# 调试用例
@bp.route('/debugcase/', methods=['POST'])
@login_required
def debugcase():
    form = DebugCaseForm(request.form)
    if form.validate():
        case_id = form.case_id.data
        case_name = form.case_name.data
        case_url = form.case_url.data
        request_method = form.request_method.data
        case_param = form.case_param.data
        expect_res = form.expect_res.data
        header = form.headers.data
        errorHeaders = is_headers_ok(header)
        if len(errorHeaders) > 0:
            collectLogs(loginfo='header头格式不正确，请检查：%s' % errorHeaders, lever='warning', logger='debugcase')
            return restful.params_error('下面的header头格式不正确，请检查！\n%s' % errorHeaders)

        if not case_id: # 新增时调试用例
            res = debugCaseFunc(method=request_method, casePath=case_url, caseParam=case_param, headers=header,
                                expect_res=expect_res, case_name=case_name)
        else: # 编辑时调试用例
            case = CaseManage.query.get(case_id)
            if not case:
                return restful.params_error(message='用例信息获取异常，通过用例id【%d】获取到的用例信息为：%s' %(case_id, case))
            res = debugCaseFunc(method=request_method, casePath=case_url, caseParam=case_param, headers=header, expect_res=expect_res, case_name=case_name)
        code = res['runCode']

        if code == 1 or code == 9:
            run_res = res['data']['runRes']
            # res_len = config.maxResponseLenth
            res_len = 200
            msg = '执行结果：%s' % (run_res)
            if len(run_res) > res_len:
                run_res = run_res[:res_len]
                msg = '执行结果长度大于%d，截取前%d个字符：%s' % (res_len, res_len, run_res)
        if code == 1:
            msg = '断言成功，预期结果：%s，%s' %(res['data']['expect_res'], msg)
            collectLogs(loginfo=msg, lever='info', logger='debugcase')
            return restful.success(message=msg)
        elif code == 9:
            msg = '断言失败，HTTP响应码：%d，预期结果：%s，%s' %(res['data']['httpCode'], res['data']['expect_res'], msg)
            collectLogs(loginfo=msg, lever='warning', logger='debugcase')
            return restful.success(message=msg)
        else:
            return restful.params_error(message='调试遇到异常，异常信息：%s'%(res))
    else:
        msg = '调试失败，原因：%s' %(form.get_errors())
        collectLogs(loginfo=msg, lever='warning', logger='debugcase')
        return restful.params_error(msg)

# 删除用例
@bp.route('/delcase/', methods=['POST'])
@login_required
def delcase():
    case_id = request.form.get('case_id')
    if not case_id:
        collectLogs(loginfo='没有找到用例id：%s' % str(case_id), lever='warning', logger='delcase')
        return restful.params_error('没有找到用例id！')
    case = CaseManage.query.get(case_id)
    if not case:
        collectLogs(loginfo='删除失败，用例不存在或已删除，id：%s' % str(case_id), lever='warning', logger='delcase')
        return restful.params_error('删除失败，没有这个用例或已删除！')
    case.isDel = 1  # 逻辑删除
    try:
        db.session.commit()
    except Exception:
        db.session.rollback()
        collectLogs(loginfo='删除失败，原因：%s' % traceback.format_exc(), lever='error', logger='delcase')
        return restful.server_error()
    collectLogs(loginfo='删除成功，用例id：%s' % str(case_id), logger='delcase')
    return restful.success('删除成功！')

# 用例计划
@bp.route('/case_plan/')
@login_required
# @permission_required(CMSPermission.CMSUSER)
def case_plan():
    tem_groups = {-1:['请选择', 0, 0, 0]}
    groups = CaseGroup.query.filter_by(isDel=0).order_by(desc('create_at')).limit(200)
    for group in groups:
        tem = []  #数据结构：tem[0]为分组名称，tem[1]为分组的总用例数，tem[2]为分组的可执行用例数
        gp_id = group.group_id
        gp_name = group.group_name
        gp_staus = group.status
        tem.append(gp_name)
        case_count_all = CaseManage.query.filter(CaseManage.group_id == gp_id).count()
        case_count_usefull = CaseManage.query.filter(CaseManage.group_id == gp_id, CaseManage.isDel==0, CaseManage.status == 1).count()
        tem.append(case_count_all)
        tem.append(case_count_usefull)
        tem.append(gp_staus)
        tem_groups[gp_id] = tem
    PER_PAGE = 10
    page = request.args.get(get_page_parameter(), type=int, default=1)
    start = (page - 1) * PER_PAGE
    end = start + PER_PAGE
    total = CasePlan.query.filter_by(isDel=0).count()
    plans = CasePlan.query.filter_by(isDel=0).order_by(desc('create_at')).slice(start, end)
    pagination = Pagination(bs_version=3, page=page, total=total, prev_label='上一页', next_label='下一页')
    context = {
        'total': total,
        'tem_groups':tem_groups,
        'plans': plans,
        'pagination': pagination
    }
    collectLogs(loginfo=context, logger='case_plan')
    return render_template('cms/case_plan.html', **context)

# 新增计划
@bp.route('/addcaseplan/', methods=['POST'])
@login_required
def addcaseplan():
    form = CreateCasePlanForm(request.form)
    if form.validate():
        plan_name = form.plan_name.data
        plan_group = form.plan_group.data
        case_run_way = form.case_run_way.data
        case_run_way_01 = form.case_run_way_01.data
        delay_time = form.delay_time.data
        create_by = g.cms_user.username
        db_plan_by_name = CasePlan.query.filter_by(plan_name=plan_name).first()
        if db_plan_by_name:
            return restful.params_error('计划名称已被占用，请更换后重新提交！')
        if not isinstance(case_run_way, int) or not isinstance(delay_time, int):
            return restful.params_error('执行方式和间隔时间必须是整数！')
        if case_run_way < 0 or case_run_way > 10000:
            return restful.params_error('执行方式必须大于等于0，并且不超过10000！')
        if delay_time < 0 or delay_time > 86400:
            return restful.params_error('间隔时间必须大于等于0，并且不超过86400！')
        if case_run_way_01 not in ['次', '分钟']:
            case_run_way_01 = '次'
        if not create_by:
            create_by = '管理员Auto'
        plan = CasePlan(plan_name=plan_name, plan_group=plan_group, case_run_way=case_run_way,
                        case_run_way_01=case_run_way_01, delay_time=delay_time, create_by=create_by,
                        update_by=create_by)
        try:
            db.session.add(plan)
            db.session.commit()
            collectLogs(loginfo='【%s】创建用例计划【%s】成功' %(create_by, plan_name), lever='info', logger='addcaseplan')
        except Exception:
            db.session.rollback()
            collectLogs(loginfo='修改用例计划失败，原因：%s' % traceback.format_exc(), lever='error', logger='addcaseplan')
            return restful.server_error()
        return restful.success('用例计划新增成功！')
    else:
        return restful.params_error(form.get_errors())

# 编辑用例计划
@bp.route('/updatecaseplan/', methods=['POST'])
@login_required
def updatecaseplan():
    form = UpdateCasePlanForm(request.form)
    if form.validate():
        plan_id = form.plan_id.data
        plan_name = form.plan_name.data
        plan_group = form.plan_group.data
        case_run_way = form.case_run_way.data
        case_run_way_01 = form.case_run_way_01.data
        delay_time = form.delay_time.data
        update_by = g.cms_user.username
        db_plan = CasePlan.query.filter_by(plan_name=plan_name).first()
        if db_plan and db_plan.plan_id != plan_id:
            collectLogs(loginfo='用例计划【%s】已存在' % (plan_name), lever='info', logger='updatecaseplan')
            return restful.params_error('计划名称已被占用，请更换后重新提交！')
        if not isinstance(case_run_way, int) or not isinstance(delay_time, int):
            return restful.params_error('执行方式和间隔时间必须是整数！')
        if case_run_way < 0 or case_run_way > 10000:
            return restful.params_error('执行方式必须大于等于0，并且不超过10000！')
        if delay_time < 0 or delay_time > 86400:
            return restful.params_error('间隔时间必须大于等于0，并且不超过86400！')
        if case_run_way_01 not in ['次', '分钟']:
            case_run_way_01 = '次'
        if not update_by:
            update_by = '管理员Auto'
        plan = CasePlan.query.get(plan_id)
        if plan:
            tem = plan.plan_name
            plan.plan_name = plan_name
            plan.plan_group = plan_group
            plan.case_run_way = case_run_way
            plan.case_run_way_01 = case_run_way_01
            plan.delay_time = delay_time
            plan.update_by = update_by
            try:
                db.session.commit()
                collectLogs(loginfo='用例计划修改成功，修改前：%s，修改后：%s，操作人：%s' % (tem, plan_name, update_by), lever='info', logger='updatecaseplan')
            except Exception:
                db.session.rollback()
                collectLogs(loginfo='修改用例计划失败，原因：%s' % traceback.format_exc(), lever='error', logger='updatecaseplan')
                return restful.server_error('用例计划信息更新失败，请重试！\n若多次重试均失败，请联系管理员处理！')
            return restful.success('修改成功！')
        else:
            collectLogs(loginfo='用plan_id查询不到记录。group_id=%s' %plan_id, lever='error', logger='updatecaseplan')
            return restful.params_error('记录不存在！')
    else:
        msg = form.get_errors()
        collectLogs(loginfo='更新异常，异常信息：%s' %msg, lever='error', logger='updatecaseplan')
        return restful.params_error(msg)

# 更新用例计划状态，执行用例，停止执行
@bp.route('/updatestatus_plan/', methods=['POST'])
@login_required
def updatestatus_plan():
    plan_id = request.form.get('plan_id')
    status = request.form.get('status')
    case_count = request.form.get('case_count')
    gp_status = request.form.get('gp_status')

    case_run_way = int(request.form.get('case_run_way'))
    case_run_way_01 = request.form.get('case_run_way_01')
    delay_time = int(request.form.get('delay_time'))

    if status == '1':
        if case_count == '0' or gp_status == '0':
            return restful.params_error('无法执行，原因：可执行用例数为0或关联分组未启用！')
    plan = CasePlan.query.get(plan_id)
    if plan:
        tem = plan.status
        plan.status = status
        groupId = plan.plan_group
        plan_id = int(plan_id)
        plan_name = plan.plan_name
        try:
            db.session.commit()
            collectLogs(loginfo='用例计划状态更新成功，更新前：%s，更新后：%s' %(tem, status), logger='updatestatus_plan')
            # 需要将获取用例的函数放到调度中，定时获取用例
            cases = getCases_sql(groupId)
            startTime = (datetime.datetime.now() + datetime.timedelta(seconds=5)).strftime("%Y-%m-%d %H:%M:%S")
            if case_run_way < 1:
                # 循环执行，走调度
                try:
                    sched.add_job(func=waysToRun, args=(cases, case_run_way, case_run_way_01, delay_time, groupId, plan_id, plan_name), trigger='interval', start_date=startTime, seconds=delay_time, id=str(plan_id))
                    collectLogs(loginfo='任务计划【%s】将在 5 秒后开始，方式：循环执行，间隔 %d 秒。' % (plan_name, delay_time), logger='updatestatus_plan')
                except:
                    pass
                if status != '1':
                    try:
                        sched.remove_job(str(plan_id))
                        collectLogs(loginfo='调度任务[id=%s]已成功移除。' % (str(plan_id), ), logger='updatestatus_plan')
                    except Exception:
                        collectLogs(loginfo='计划成功停止，但是调度任务[id=%s]移除失败，原因：%s' %(str(plan_id), traceback.format_exc()), lever='error', logger='updatestatus_plan')
                        return restful.server_error('计划状态更新成功，调度任务停止失败！')
                collectLogs(loginfo='当前在执行中的调度任务list：%s' % (sched.get_jobs(),))

            else:
                # 指定时间执行
                if case_run_way_01 == '分钟':
                    try:
                        endTime = startTime + (datetime.datetime.now() + datetime.timedelta(seconds=5, minutes=case_run_way)).strftime("%Y-%m-%d %H:%M:%S")
                        sched.add_job(func=waysToRun, args=(cases, case_run_way, case_run_way_01, delay_time, groupId, plan_id, plan_name), trigger='interval', start_date=startTime, end_date=endTime, seconds=delay_time, id=str(plan_id))
                        collectLogs(loginfo='任务计划【%s】将在 5 秒后开始，方式：执行 %d 分钟，间隔 %d 秒。' % (plan_name, case_run_way, delay_time), logger='updatestatus_plan')
                    except:
                        pass
                    if status != '1':
                        try:
                            sched.remove_job(str(plan_id))
                            collectLogs(loginfo='调度任务[id=%s]已成功移除。' % (str(plan_id),), logger='updatestatus_plan')
                        except Exception:
                            collectLogs(loginfo='计划成功停止，但是调度任务[id=%s]移除失败，原因：%s' % (str(plan_id), traceback.format_exc()), lever='error', logger='updatestatus_plan')
                            return restful.server_error('计划状态更新成功，调度任务停止失败！')
                    collectLogs(loginfo='当前在执行中的调度任务list：%s' % (sched.get_jobs(),))

                # 按次数执行
                else:
                    try:
                        sched.add_job(func=waysToRun, args=(cases, case_run_way, case_run_way_01, delay_time, groupId, plan_id, plan_name), trigger='date', run_date=startTime, id=str(plan_id))
                        collectLogs(loginfo='任务计划【%s】将在 5 秒后开始执行，方式：执行 %d 次，间隔 %d 秒。' % (plan_name, case_run_way, delay_time), logger='updatestatus_plan')
                    except Exception:
                        pass
                    if status != '1':
                        try:
                            sched.remove_job(str(plan_id))
                            collectLogs(loginfo='调度任务[id=%s]已成功移除。' % (str(plan_id),), logger='updatestatus_plan')
                        except Exception:
                            collectLogs(loginfo='计划成功停止，但是调度任务[id=%s]移除失败，原因：%s' % (str(plan_id), traceback.format_exc()), lever='error', logger='updatestatus_plan')
                            return restful.server_error('计划状态更新成功，调度任务停止失败！')
                    collectLogs(loginfo='当前在执行中的调度任务list：%s' % (sched.get_jobs(),))
        except Exception:
            db.session.rollback()
            collectLogs(loginfo='更新用例计划状态失败，原因：%s' % traceback.format_exc(), lever='error', logger='updatestatus_plan')
            return restful.server_error('用例计划状态更新失败，请重试！\n若多次重试均失败，请联系管理员处理！')
        return restful.success()
    else:
        collectLogs(loginfo='更新用例计划状态失败，用例计划不存在！，用例计划id：%s' % str(plan_id), lever='warning', logger='updatestatus_plan')
        return restful.params_error('用例计划不存在！')

# 删除用例计划
@bp.route('/delcaseplan/', methods=['POST'])
@login_required
def delcaseplan():
    plan_id = request.form.get('plan_id')
    if not plan_id:
        collectLogs(loginfo='没有找到用例计划id：%s' % str(plan_id), lever='warning', logger='delcaseplan')
        return restful.params_error('没有找到用例计划id！')
    plan = CasePlan.query.get(plan_id)
    if not plan:
        collectLogs(loginfo='删除失败，用例计划不存在或已删除，id：%s' % str(plan_id), lever='warning', logger='delcaseplan')
        return restful.params_error('删除失败，没有这个用例计划或已删除！')
    if plan.status == 1:
        collectLogs(loginfo='正在运行的用例计划不允许删除，请停止后再删除，id：%s' % str(plan_id), lever='warning', logger='delcaseplan')
        return restful.params_error('计划正在运行，不允许删除！')
    plan.isDel = 1  # 逻辑删除
    try:
        db.session.commit()
        collectLogs(loginfo='删除成功，用例计划id：%s' % str(plan_id), logger='delcaseplan')
    except Exception:
        db.session.rollback()
        collectLogs(loginfo='删除失败，原因：%s' % traceback.format_exc(), lever='error', logger='delcaseplan')
        return restful.server_error()
    return restful.success('删除成功！')

# 用例执行结果
@bp.route('/case_execute_res/')
@login_required
# @permission_required(CMSPermission.CMSUSER)
def case_execute_res():
    query_case_name = request.args.get('p1', type=str, default='')
    query_plan = request.args.get('p2', type=str, default='0')
    query_res_status = request.args.get('p3', type=str, default='0')
    res = ExecuteSql(1, query_case_name, query_plan, query_res_status)
    if not res:
        res = ()
    planDict = {}
    plans = CasePlan.query.filter_by().limit(200)
    for plan in plans:
        temList = []
        temList.append(plan.plan_name)
        temList.append(plan.isDel)
        planDict[plan.plan_id] = temList
    cases = db.session.query(CaseManage.case_id, CaseManage.case_name).filter_by(isDel=0).all()  #获取用例id和用例名字
    casesDict = dict(cases)   # cases是元素为两元素元组的列表，可以直接转化为字典

    PER_PAGE = 10
    page = request.args.get(get_page_parameter(), type=int, default=1)
    start = (page - 1) * PER_PAGE
    end = start + PER_PAGE
    total = len(res)
    res = res[start:end]
    pagination = Pagination(bs_version=3, page=page, total=total, prev_label='上一页', next_label='下一页')
    context = {
        'res': res,
        'total':total,
        'planDict':planDict,
        'casesDict':casesDict,
        'pagination': pagination
    }
    return render_template('cms/case_execute_res.html', **context)

# 图形化展示执行结果统计
@bp.route('/piecharts/')
def pieCharts():
    return render_template('chartsHtml/showResult_pie.html')

@bp.route("/piecharts_data/")
def get_pie_chart():
    c = myCharts([], chartType='pie', dataFrom='inner')
    return c.dump_options_with_quotes()

# ########################### sparrow接口自动化相关的代码到这截止 ################################

bp.add_url_rule('/login/', view_func=LoginView.as_view('login'))
bp.add_url_rule('/resetpwd/', view_func=ResetPwdView.as_view('resetpwd'))
bp.add_url_rule('/resetemail/', view_func=ResetEmailView.as_view('resetemail'))
bp.add_url_rule('/addcase/', view_func=addCaseView.as_view('addcase'))

# 自定义模版过滤器：截取字符串
@bp.app_template_filter('mycut')
def mycut(x, start, end):
    x = str(x)[start:end]
    return x

@bp.app_template_filter('is_number')
def is_number(x):
    x = str(x)
    if x.isdigit():
        return 1
    else:
        return 0

@bp.app_template_filter('is_imgOrVideo')
def is_imgOrVideo(x):
    x = str(x)
    if '.jpg' in x or '.jpeg' in x or '.png' in x or '.gif' in x or '.ico' in x or '.mp4' in x or '.avi' in x or '.flv' in x or '.mov' in x:
        return 1
    else:
        return 0

@bp.app_template_filter('filterNumbers')
def filterNumbers(targetStr):
    result = [int(s) for s in re.findall(r'\b\d+\b', str(targetStr))]
    return result
